﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;
using ClassLibrary01;

namespace Server
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

        [WebMethod]
        public List<Medico> MedicoPorEspecialidade(int cod_esp)
        {
            List<Medico> lista = new List<Medico>();

            string cstr = "Server=localhost;Database=intermed;User Id=sa;Password=sa!@#";
            using (SqlConnection conn = new SqlConnection(cstr))
            {
                conn.Open();               
                string query = "select medico.nome from medico inner join medico_especialidade on medico_especialidade.cod_med=medico.cod_med and medico_especialidade.cod_esp=@name";
                SqlCommand cmd = new SqlCommand(query, conn);
                SqlParameter name = cmd.Parameters.Add("@name", SqlDbType.Int);
                name.Value =cod_esp;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Medico m = new Medico();
                    m.Nome = dr.GetSqlValue(0).ToString();
                    lista.Add(m);
                }
                conn.Close();
            }
            return lista;
            
        }
    }
}